package com.itholmes.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.itholmes.pojo.Emp;
import com.itholmes.pojo.User;
import com.itholmes.utils.JDBCUtils;

public class EmpDaoImpl implements EmpDao{

	//插入操作
	public int empAdd(Emp emp, User user) {
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement pre = null;
		int i = 0;
		try {
			pre = connection.prepareStatement("insert into `emp` (ename,job,mgr,hiredate,sal,COMM,deptno) values(?,?,?,?,?,?,?)");
			pre.setString(1, emp.getEname());
			pre.setString(2, emp.getJob());
			pre.setInt(3, emp.getMgr());
			pre.setDate(4, emp.getHiredate());
			pre.setBigDecimal(5, emp.getSal());
			pre.setBigDecimal(6, emp.getCOMM());
			pre.setInt(7, emp.getDeptno());
			
			i = pre.executeUpdate();
			
			if(i>0) {
				System.out.println("插入操作成功");
			}
			
			//演示一个错误效果
			//int a = 1/0;
			//提交事务
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtils.release(null, pre, null);
		}
		return i;
	}

	
	//删除操作
	public int empDelete(int empno, User user) {
		
		Connection connection = JDBCUtils.getConnection();
		
		PreparedStatement pre = null;
		int i = 0;
		
		try {
			pre = connection.prepareStatement("delete from emp where empno = ?");
			pre.setInt(1, empno);
			i = pre.executeUpdate();
			
			if(i>0) {
				System.out.println("删除成功");
			}
			
			//故意报错！！
			//int a = 1/0;
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			
		}finally{
			JDBCUtils.release(null, pre, null);		
		}
		
		return i;
	}

	//更新操作
	public int empUpdate(Emp emp, User user) {
		
		Connection connection = JDBCUtils.getConnection();
		
		PreparedStatement pre = null;
		int i = 0;
		try {
			
			pre = connection.prepareStatement("update emp set ename=?,job=?,mgr=?,hiredate=?,sal=?,COMM=?,deptno=? where empno=?");
			pre.setString(1, emp.getEname());
			pre.setString(2, emp.getJob());
			pre.setInt(3, emp.getMgr());
			pre.setDate(4, emp.getHiredate());
			pre.setBigDecimal(5, emp.getSal());
			pre.setBigDecimal(6, emp.getCOMM());
			pre.setInt(7, emp.getDeptno());
			pre.setInt(8, emp.getEmpno());
			
			i = pre.executeUpdate();
			
//			int a = 1/0;
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		JDBCUtils.release(null, pre, null);
		return i;
	}

	//查询emp
	public List<Emp> empSelect(User user) {
		
		List<Emp> list = new ArrayList<Emp>();
		Connection connection = JDBCUtils.getConnection();
		PreparedStatement pre = null;
		ResultSet rs = null;
		
		try {
			pre = connection.prepareStatement("select * from emp");
			rs = pre.executeQuery();
			while(rs.next()) {
				Emp emp = new Emp();
				emp.setEmpno(rs.getInt("empno"));
				emp.setEname(rs.getString("ename"));
				emp.setJob(rs.getString("job"));
				emp.setMgr(rs.getInt("mgr"));
				emp.setHiredate(rs.getDate("hiredate"));
				emp.setSal(rs.getBigDecimal("sal"));
				emp.setCOMM(rs.getBigDecimal("COMM"));
				emp.setDeptno(rs.getInt("deptno"));
				list.add(emp);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		JDBCUtils.release(null, pre, rs);
		
		return list;
	}
	
}
